--ODS到DWD的shell脚本

#! /bin/bash

 

#上个月1日

Last_Month_DATE=(date -d "(date +%Y%m)01 last month" +%Y-%m-01)

 

out_put=({HIVE_HOME} -S -e "

--分区

SET hive.exec.dynamic.partition=true;

SET hive.exec.dynamic.partition.mode=nonstrict;

set hive.exec.max.dynamic.partitions.pernode=10000;

set hive.exec.max.dynamic.partitions=100000;

set hive.exec.max.created.files=150000;

--hive压缩

set hive.exec.compress.intermediate=true;

set hive.exec.compress.output=true;

--写入时压缩生效

set hive.exec.orc.compression.strategy=COMPRESSION;

--分桶

set hive.enforce.bucketing=true;

set hive.enforce.sorting=true;

set hive.optimize.bucketmapjoin = true;

set hive.auto.convert.sortmerge.join=true;

set hive.auto.convert.sortmerge.join.noconditionaltask=true;

--并行执行

set hive.exec.parallel=true;

set hive.exec.parallel.thread.number=8;

--矢量化查询

set hive.vectorized.execution.enabled=true;

--关联优化器

set hive.optimize.correlation=true;

--读取零拷贝

set hive.exec.orc.zerocopy=true;

 

INSERT INTO itcast_dwd.customer_relationship_dwd PARTITION(payment_time_year, payment_time_month, payment_time_day)

SELECT

    id,

    deleted,

    customer_id,

    nvl(origin_type, -1) origin_type,

    payment_state,

    payment_time,

    substr(payment_time, 12, 2) as payment_time_hour,

    nvl(itcast_clazz_id, -1) itcast_clazz_id,

    nvl(creator, -1) creator,

    if(origin_type='NETSERVICE' or origin_type='PRESIGNUP', '1', '0') origin_type_stat,

    substr(payment_time, 1, 4) payment_time_year,

    substr(payment_time, 6, 2) payment_time_month,

    substr(payment_time, 9, 2) payment_time_day

from itcast_ods.customer_relationship

WHERE deleted = 0 AND customer_id IS NOT NULL AND payment_state='PAID'

and substr(payment_time, 1, 10) >='${Last_Month_DATE}';")



--DWD到DWM的shell脚本



#! /bin/bash

HIVE_HOME=/usr/bin/hive

 

Last_Month=(date -d "(date +%Y%m)01 last month" +%Y-%m-01)

${HIVE_HOME} -S -e "

--分区

SET hive.exec.dynamic.partition=true;

SET hive.exec.dynamic.partition.mode=nonstrict;

set hive.exec.max.dynamic.partitions.pernode=10000;

set hive.exec.max.dynamic.partitions=100000;

set hive.exec.max.created.files=150000;

--hive压缩

set hive.exec.compress.intermediate=true;

set hive.exec.compress.output=true;

--写入时压缩生效

set hive.exec.orc.compression.strategy=COMPRESSION;

--分桶

set hive.enforce.bucketing=true;

set hive.enforce.sorting=true;

set hive.optimize.bucketmapjoin = true;

set hive.auto.convert.sortmerge.join=true;

set hive.auto.convert.sortmerge.join.noconditionaltask=true;

--并行执行

set hive.exec.parallel=true;

set hive.exec.parallel.thread.number=8;

--矢量化查询

set hive.vectorized.execution.enabled=true;

--关联优化器

set hive.optimize.correlation=true;

--读取零拷贝

set hive.exec.orc.zerocopy=true;

--join数据倾斜

set hive.optimize.skewjoin=true;

-- set hive.skewjoin.key=100000;

set hive.optimize.skewjoin.compiletime=true;

set hive.optimize.union.remove=true;

 

INSERT into itcast_dwm.customer_signup_dwm PARTITION (payment_time_year, payment_time_month, payment_time_day)

SELECT

    dwd.customer_id,

    clazz.itcast_school_id,

    clazz.itcast_school_name,

    clazz.itcast_subject_id,

    clazz.itcast_subject_name,

    e.tdepart_id,

    dept.name as tdepart_name,

    dwd.origin_type,

    dwd.origin_type_stat,

    dwd.payment_time_hour,

    dwd.payment_time_year,

    dwd.payment_time_month,

    dwd.payment_time_day

FROM itcast_dwd.customer_relationship_dwd dwd

LEFT JOIN itcast_dimen.itcast_clazz clazz on dwd.itcast_clazz_id=clazz.id

LEFT JOIN itcast_dimen.employee e on dwd.creator=e.id

LEFT JOIN itcast_dimen.scrm_department dept on e.tdepart_id=dept.id

where substr(dwd.payment_time, 1, 10) >= '$Last_Month' ;"

--DWM到DWS的shell脚本

#! /bin/bash

HIVE_HOME=/usr/bin/hive

Last_Month=(date -d "(date +%Y%m)01 last month" +%Y-%m-01)

${HIVE_HOME} -S -e "

--分区

SET hive.exec.dynamic.partition=true;

SET hive.exec.dynamic.partition.mode=nonstrict;

set hive.exec.max.dynamic.partitions.pernode=10000;

set hive.exec.max.dynamic.partitions=100000;

set hive.exec.max.created.files=150000;

--hive压缩

set hive.exec.compress.intermediate=true;

set hive.exec.compress.output=true;

--写入时压缩生效

set hive.exec.orc.compression.strategy=COMPRESSION;

--分桶

set hive.enforce.bucketing=true;

set hive.enforce.sorting=true;

set hive.optimize.bucketmapjoin = true;

set hive.auto.convert.sortmerge.join=true;

set hive.auto.convert.sortmerge.join.noconditionaltask=true;

--并行执行

set hive.exec.parallel=true;

set hive.exec.parallel.thread.number=8;

--矢量化查询

set hive.vectorized.execution.enabled=true;

--关联优化器

set hive.optimize.correlation=true;

--读取零拷贝

set hive.exec.orc.zerocopy=true;

--join数据倾斜

set hive.optimize.skewjoin=true;

-- set hive.skewjoin.key=100000;

set hive.optimize.skewjoin.compiletime=true;

set hive.optimize.union.remove=true;

-- group倾斜

set hive.groupby.skewindata=true;



    --校区、学科组合分组
    

--小时

INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)

SELECT

    count(dwm.customer_id) as signup_num,
    dwm.itcast_school_id,
    dwm.itcast_school_name,
    dwm.itcast_subject_id,
    dwm.itcast_subject_name,
    -1 as tdepart_id,
    '-1' tdepart_name,
    '-1' origin_type,
    dwm.origin_type_stat,
    dwm.payment_time_hour,
    '1' grouptype, 
    '1' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    dwm.payment_time_day
    

from itcast_dwm.customer_signup_dwm dwm

where CONCAT_WS('-',dwm.payment_time_year,dwm.payment_time_month,dwm.payment_time_day) >= '2037-02-01'

GROUP BY dwm.origin_type_stat,

    dwm.payment_time_year, dwm.payment_time_month, dwm.payment_time_day, dwm.payment_time_hour,
    dwm.itcast_school_id, dwm.itcast_school_name, dwm.itcast_subject_id, dwm.itcast_subject_name;
    

--天

INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)

SELECT

    count(dwm.customer_id) as signup_num,
    dwm.itcast_school_id,
    dwm.itcast_school_name,
    dwm.itcast_subject_id,
    dwm.itcast_subject_name,
    -1 as tdepart_id,
    '-1' tdepart_name,
    '-1' origin_type,
    dwm.origin_type_stat,
    '-1' payment_time_hour,
    '1' grouptype, 
    '2' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    dwm.payment_time_day
    

from itcast_dwm.customer_signup_dwm dwm

where CONCAT_WS('-',dwm.payment_time_year,dwm.payment_time_month,dwm.payment_time_day) >= '2037-02-01'

GROUP BY dwm.origin_type_stat,

    dwm.payment_time_year, dwm.payment_time_month, dwm.payment_time_day, 
    dwm.itcast_school_id, dwm.itcast_school_name, dwm.itcast_subject_id, dwm.itcast_subject_name;
    

--月

INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)

SELECT

    count(dwm.customer_id) as signup_num,
    dwm.itcast_school_id,
    dwm.itcast_school_name,
    dwm.itcast_subject_id,
    dwm.itcast_subject_name,
    -1 as tdepart_id,
    '-1' tdepart_name,
    '-1' origin_type,
    dwm.origin_type_stat,
    '-1' payment_time_hour,
    '1' grouptype, 
    '3' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    dwm.payment_time_day
    

from itcast_dwm.customer_signup_dwm dwm

where CONCAT_WS('-',dwm.payment_time_year,dwm.payment_time_month,dwm.payment_time_day) >= '2037-02-01'

GROUP BY dwm.origin_type_stat,

    dwm.payment_time_year, dwm.payment_time_month, 
    dwm.itcast_school_id, dwm.itcast_school_name, dwm.itcast_subject_id, dwm.itcast_subject_name;
    

--年

INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)

SELECT

    count(dwm.customer_id) as signup_num,
    dwm.itcast_school_id,
    dwm.itcast_school_name,
    dwm.itcast_subject_id,
    dwm.itcast_subject_name,
    -1 as tdepart_id,
    '-1' tdepart_name,
    '-1' origin_type,
    dwm.origin_type_stat,
    '-1' payment_time_hour,
    '1' grouptype, 
    '4' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    dwm.payment_time_day
    

from itcast_dwm.customer_signup_dwm dwm

where CONCAT_WS('-',dwm.payment_time_year,dwm.payment_time_month,dwm.payment_time_day) >= '2037-02-01'

GROUP BY dwm.origin_type_stat,

    dwm.payment_time_year,  
    dwm.itcast_school_id, dwm.itcast_school_name, dwm.itcast_subject_id, dwm.itcast_subject_name;
    



--来源渠道分组

--小时

INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)

SELECT

    count(dwm.customer_id) as signup_num,
    '-1' itcast_school_id,
    '-1' itcast_school_name,
    '-1' itcast_subject_id,
    '-1' itcast_subject_name,
    -1 as tdepart_id,
    '-1' tdepart_name,
    dwm.origin_type,
    dwm.origin_type_stat,
    dwm.payment_time_hour,
    '2' grouptype, 
    '1' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    dwm.payment_time_day
    

from itcast_dwm.customer_signup_dwm dwm

where CONCAT_WS('-',dwm.payment_time_year,dwm.payment_time_month,dwm.payment_time_day) >= '2037-02-01'

GROUP BY dwm.origin_type_stat,

    dwm.payment_time_year, dwm.payment_time_month, dwm.payment_time_day, dwm.payment_time_hour,
    dwm.origin_type;
    

--天

INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)

SELECT

    count(dwm.customer_id) as signup_num,
    '-1' itcast_school_id,
    '-1' itcast_school_name,
    '-1' itcast_subject_id,
    '-1' itcast_subject_name,
    -1 as tdepart_id,
    '-1' tdepart_name,
    dwm.origin_type,
    dwm.origin_type_stat,
    '-1' payment_time_hour,
    '2' grouptype, 
    '2' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    dwm.payment_time_day
    

from itcast_dwm.customer_signup_dwm dwm

where CONCAT_WS('-',dwm.payment_time_year,dwm.payment_time_month,dwm.payment_time_day) >= '2037-02-01'

GROUP BY dwm.origin_type_stat,

    dwm.payment_time_year, dwm.payment_time_month, dwm.payment_time_day,
    dwm.origin_type;
    

--月

INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)

SELECT

    count(dwm.customer_id) as signup_num,
    '-1' itcast_school_id,
    '-1' itcast_school_name,
    '-1' itcast_subject_id,
    '-1' itcast_subject_name,
    -1 as tdepart_id,
    '-1' tdepart_name,
    dwm.origin_type,
    dwm.origin_type_stat,
    '-1' payment_time_hour,
    '2' grouptype, 
    '3' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    dwm.payment_time_day
    

from itcast_dwm.customer_signup_dwm dwm

where CONCAT_WS('-',dwm.payment_time_year,dwm.payment_time_month,dwm.payment_time_day) >= '2037-02-01'

GROUP BY dwm.origin_type_stat,

    dwm.payment_time_year, dwm.payment_time_month,
    dwm.origin_type;
    

--年

INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)

SELECT

    count(dwm.customer_id) as signup_num,
    '-1' itcast_school_id,
    '-1' itcast_school_name,
    '-1' itcast_subject_id,
    '-1' itcast_subject_name,
    -1 as tdepart_id,
    '-1' tdepart_name,
    dwm.origin_type,
    dwm.origin_type_stat,
    '-1' payment_time_hour,
    '2' grouptype, 
    '4' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    dwm.payment_time_day
    

from itcast_dwm.customer_signup_dwm dwm

where CONCAT_WS('-',dwm.payment_time_year,dwm.payment_time_month,dwm.payment_time_day) >= '2037-02-01'

GROUP BY dwm.origin_type_stat,

    dwm.payment_time_year, 
    dwm.origin_type;
    

--咨询中心分组

--小时

INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)

SELECT

    count(dwm.customer_id) as signup_num,
    '-1' itcast_school_id,
    '-1' itcast_school_name,
    '-1' itcast_subject_id,
    '-1' itcast_subject_name,
    dwm.tdepart_id,
    dwm.tdepart_name,
    '-1' origin_type,
    dwm.origin_type_stat,
    dwm.payment_time_hour,
    '3' grouptype, 
    '1' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    dwm.payment_time_day
    

from itcast_dwm.customer_signup_dwm dwm

where CONCAT_WS('-',dwm.payment_time_year,dwm.payment_time_month,dwm.payment_time_day) >= '2037-02-01'

GROUP BY dwm.origin_type_stat,

    dwm.payment_time_year, dwm.payment_time_month, dwm.payment_time_day, dwm.payment_time_hour,
    dwm.tdepart_id, dwm.tdepart_name;
    

--天

INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)

SELECT

    count(dwm.customer_id) as signup_num,
    '-1' itcast_school_id,
    '-1' itcast_school_name,
    '-1' itcast_subject_id,
    '-1' itcast_subject_name,
    dwm.tdepart_id,
    dwm.tdepart_name,
    '-1' origin_type,
    dwm.origin_type_stat,
    '-1' payment_time_hour,
    '3' grouptype, 
    '2' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    dwm.payment_time_day
    

from itcast_dwm.customer_signup_dwm dwm

where CONCAT_WS('-',dwm.payment_time_year,dwm.payment_time_month,dwm.payment_time_day) >= '2037-02-01'

GROUP BY dwm.origin_type_stat,

    dwm.payment_time_year, dwm.payment_time_month, dwm.payment_time_day,
    dwm.tdepart_id, dwm.tdepart_name;
    

--月

INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)

SELECT

    count(dwm.customer_id) as signup_num,
    '-1' itcast_school_id,
    '-1' itcast_school_name,
    '-1' itcast_subject_id,
    '-1' itcast_subject_name,
    dwm.tdepart_id,
    dwm.tdepart_name,
    '-1' origin_type,
    dwm.origin_type_stat,
    '-1' payment_time_hour,
    '3' grouptype, 
    '3' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    dwm.payment_time_day
    

from itcast_dwm.customer_signup_dwm dwm

where CONCAT_WS('-',dwm.payment_time_year,dwm.payment_time_month,dwm.payment_time_day) >= '2037-02-01'

GROUP BY dwm.origin_type_stat,

    dwm.payment_time_year, dwm.payment_time_month, 
    dwm.tdepart_id, dwm.tdepart_name;
    

--年

INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)

SELECT

    count(dwm.customer_id) as signup_num,
    '-1' itcast_school_id,
    '-1' itcast_school_name,
    '-1' itcast_subject_id,
    '-1' itcast_subject_name,
    dwm.tdepart_id,
    dwm.tdepart_name,
    '-1' origin_type,
    dwm.origin_type_stat,
    '-1' payment_time_hour,
    '3' grouptype, 
    '4' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    dwm.payment_time_day
    

from itcast_dwm.customer_signup_dwm dwm

where CONCAT_WS('-',dwm.payment_time_year,dwm.payment_time_month,dwm.payment_time_day) >= '2037-02-01'

GROUP BY dwm.origin_type_stat,

    dwm.payment_time_year,
    dwm.tdepart_id, dwm.tdepart_name;
    

--总数分组

--小时

INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)

SELECT

    count(dwm.customer_id) as signup_num,
    '-1' itcast_school_id,
    '-1' itcast_school_name,
    '-1' itcast_subject_id,
    '-1' itcast_subject_name,
    -1 as tdepart_id,
    '-1' tdepart_name,
    '-1' origin_type,
    dwm.origin_type_stat,
    dwm.payment_time_hour,
    '4' grouptype, 
    '1' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    dwm.payment_time_day
    

from itcast_dwm.customer_signup_dwm dwm

where CONCAT_WS('-',dwm.payment_time_year,dwm.payment_time_month,dwm.payment_time_day) >= '2037-02-01'

GROUP BY dwm.origin_type_stat,

    dwm.payment_time_year, dwm.payment_time_month, dwm.payment_time_day, dwm.payment_time_hour;
    

--天

INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)

SELECT

    count(dwm.customer_id) as signup_num,
    '-1' itcast_school_id,
    '-1' itcast_school_name,
    '-1' itcast_subject_id,
    '-1' itcast_subject_name,
    -1 as tdepart_id,
    '-1' tdepart_name,
    '-1' origin_type,
    dwm.origin_type_stat,
    '-1' payment_time_hour,
    '4' grouptype, 
    '2' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    dwm.payment_time_day
    

from itcast_dwm.customer_signup_dwm dwm

where CONCAT_WS('-',dwm.payment_time_year,dwm.payment_time_month,dwm.payment_time_day) >= '2037-02-01'

GROUP BY dwm.origin_type_stat,

    dwm.payment_time_year, dwm.payment_time_month, dwm.payment_time_day;
    

--月

INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)

SELECT

    count(dwm.customer_id) as signup_num,
    '-1' itcast_school_id,
    '-1' itcast_school_name,
    '-1' itcast_subject_id,
    '-1' itcast_subject_name,
    -1 as tdepart_id,
    '-1' tdepart_name,
    '-1' origin_type,
    dwm.origin_type_stat,
    '-1' payment_time_hour,
    '4' grouptype, 
    '3' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    dwm.payment_time_day
    

from itcast_dwm.customer_signup_dwm dwm

where CONCAT_WS('-',dwm.payment_time_year,dwm.payment_time_month,dwm.payment_time_day) >= '2037-02-01'

GROUP BY dwm.origin_type_stat,

    dwm.payment_time_year, dwm.payment_time_month;
    

--年

INSERT INTO itcast_dws.customer_signup_dws PARTITION(payment_time_year, payment_time_month, payment_time_day)

SELECT

    count(dwm.customer_id) as signup_num,
    '-1' itcast_school_id,
    '-1' itcast_school_name,
    '-1' itcast_subject_id,
    '-1' itcast_subject_name,
    -1 as tdepart_id,
    '-1' tdepart_name,
    '-1' origin_type,
    dwm.origin_type_stat,
    '-1' payment_time_hour,
    '4' grouptype, 
    '4' as time_type,
    dwm.payment_time_year,
    dwm.payment_time_month,
    dwm.payment_time_day
    

from itcast_dwm.customer_signup_dwm dwm

where CONCAT_WS('-',dwm.payment_time_year,dwm.payment_time_month,dwm.payment_time_day) >= '2037-02-01'

GROUP BY dwm.origin_type_stat,

    dwm.payment_time_year;





--导出数据的shell脚本
#! /bin/bash
SQOOP_HOME=/usr/bin/sqoop
HOST=172.17.0.202
USERNAME="root"
PASSWORD="123456"
PORT=3306
DBNAME="scrm_bi"
MYSQL=/usr/local/mysql_5723/bin/mysql
if [[ $1 == "" ]];then
   TD_DATE=`date -d '1 days ago' "+%Y-%m-%d"`
else
   TD_DATE=$1
fi
${MYSQL} -h${HOST} -P${PORT} -u${USERNAME} -p${PASSWORD} -D${DBNAME} -e "delete from customer_signup_app where payment_time_year = '${TD_DATE:0:4}'"
${SQOOP_HOME} export \
--connect "jdbc:mysql://${HOST}:${PORT}/${DBNAME}?useUnicode=true&characterEncoding=utf-8" \
--username ${USERNAME} \
--password ${PASSWORD} \
--table customer_signup_app \
--hcatalog-database itcast_dws \
--hcatalog-table customer_signup_dws \
--hcatalog-partition-keys payment_time_year  \
--hcatalog-partition-values ${TD_DATE:0:4}  \
-m 10

